// DwarfMgr.java
import java.util.*;
import Database;
import DbRowSet;
import Dwarf;

public class DwarfMgr {
  private static String select =
        "select d.dwarf_id as dwarf_id," +
             "d.dwarf_name as dwarf_name," +
             "d.born as born," +
             "d.home_id as home_id," +
             "mountain_name as home_name," +
             "d.spouse_id," +
             "s.dwarf_name as spouse_name " +
           "from dwarf d " +
           "left join dwarf s on d.spouse_id = s.dwarf_id " +
           "left join mountain m on d.home_id = m.mountain_id ";

  public static List GetAll() throws java.sql.SQLException {
    DbRowSet res = Database.RunSql(select);
    List ret = new LinkedList();

    while(res.next()){
      Dwarf d = new Dwarf(res.getInt("dwarf_id"),
                             res.getString("dwarf_name"),
                             res.getInt("born"),
                             res.getInt("home_id"),
                             res.getString("home_name"),
                             res.getInt("spouse_id"),
                             res.getString("spouse_name"));
      ret.add(d);
    } 

    return ret;
  } 

  public static Dwarf Get(int dwarf_id) throws java.sql.SQLException {
    String lselect = select + " where d.dwarf_id = ?";
    List params = new LinkedList();
    params.add(new Integer(dwarf_id));
    DbRowSet res = Database.RunSql(lselect,params);
    Dwarf d = null;

    while(res.next()){
      d = new Dwarf(res.getInt("dwarf_id"),
                             res.getString("dwarf_name"),
                             res.getInt("born"),
                             res.getInt("home_id"),
                             res.getString("home_name"),
                             res.getInt("spouse_id"),
                             res.getString("spouse_name"));
    }

    return d;
  } 

  public static int Add(String dwarf_name,
                 int born,
                 int home_id) throws java.sql.SQLException
  {
    List params = new LinkedList();
    params.add(dwarf_name);
    params.add(new Integer(born));

    if(home_id != 0)
      params.add(new Integer(home_id));
    else
      params.add(null);

    Database.RunSql(
      "insert into dwarf (dwarf_name,born,home_id) values (?,?,?)",
      params);

    // return dwarf_id later...
    return 0;
  }

  public static void Update(int dwarf_id, String dwarf_name,
                            int born, int home_id) 
    throws java.sql.SQLException
  {
    List params = new LinkedList();
    params.add(dwarf_name);
    params.add(new Integer(born));

    if(home_id != 0)
      params.add(new Integer(home_id));
    else     
      params.add(null);

    params.add(new Integer(dwarf_id));

    Database.RunSql(
      "update dwarf set dwarf_name = ?,"+
            "born = ?," +
            "home_id = ? " +
          "where dwarf_id = ?",
      params);
  }
}